package com.znn.sp;

import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;


public class DATA {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		System.out.println(getAllUser());
	}
	/* 获取数据库连接的函数*/  
    public static Connection getConnection() {  
        Connection con = null;  //创建用于连接数据库的Connection对象  
        try {  
            Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动  
              
            con = DriverManager.getConnection(  
                    "jdbc:mysql://localhost:3306/qd?useUnicode=true&characterEncoding=UTF-8", "root", "123456");// 创建数据连接  
              
        } catch (Exception e) {  
            System.out.println("数据库连接失败" + e.getMessage());  
        }  
        return con; //返回所建立的数据库连接  
    } 
    
    public static String  getAllUser() {
		Connection connection = getConnection();
		String sql = "select * from qdzs_user";
		StringBuffer sBuffer = new StringBuffer();
		try {
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				sBuffer.append("id-"+resultSet.getInt("id"));
				sBuffer.append(" uname-"+resultSet.getString("uname")+"\n");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sBuffer.toString();
	}

    public static void writeToData(String aid,String buildingid, String clazz, String whichweek,String week,List<KJS> list) {
    	Connection connection = getConnection();
    	Type type = new TypeToken<List<KJS>>(){}.getType();
		String reString = new Gson().toJson(list,type);
		PreparedStatement stmt = null;
		String sql = "insert into kjs (aid,buildingid,class,whichweek,week,dataj) values(?,?,?,?,?,?)";
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, aid);
			stmt.setString(2, buildingid);
			stmt.setString(3, clazz);
			stmt.setString(4, whichweek);
			stmt.setString(5, week);
			stmt.setString(6, reString);
//			System.out.println(reString);
			boolean b = stmt.execute();
			if (b) {
				System.out.println(aid+" "+buildingid+" "+clazz+" "+whichweek+" "+week+" 写入成功!");
			}else {
				System.out.println(aid+" "+buildingid+" "+clazz+" "+whichweek+" "+week+" 写入成功!");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
